select create_corpora(10);


select * from t_corpus;

select * from t_lang_resource where lr_id = 11;
select * from t_lang_resource where lr_type_id = 2;
select max(lr_id) from t_lang_resource;
select currval('seq_lang_resource');

select *  from t_corpus_document where cd_corp_id = 17 limit 10;

delete from t_corpus_document where cd_corp_id  > 11;
delete from t_corpus where corp_lr_id > 61705;
delete from t_lang_resource where lr_id  > 61705;

select count(*), cd_corp_id from t_corpus_document group by cd_corp_id;


select * from t_lr_type;


# SYNC A SEQUENCE
CREATE OR REPLACE FUNCTION sync_seq_lang_resource() RETURNS VOID AS '
DECLARE
   v_lr_id INTEGER;
   v_maxlr_id INTEGER;
BEGIN
        SELECT MAX(lr_id) INTO v_maxlr_id FROM t_lang_resource;
LOOP
   SELECT nextval(''seq_lang_resource'') INTO v_lr_id;
   IF v_lr_id >= v_maxlr_id THEN
       EXIT;
   END IF;
END LOOP;
END;
' LANGUAGE 'plpgsql';
SELECT reset_seq_lang_resource();
#END SYNC A SEQUENCE


-- Get the remarks field of a ticket
SELECT dc_character_content 
FROM t_doc_content, t_document
    WHERE  doc_content_id = dc_id 
    AND doc_url LIKE '%ME97002117.txt';

-- Get all corpus names and the number of documents each contains.
SELECT lr.lr_name, count(*) 
FROM t_document d 
   INNER JOIN t_corpus_document cd ON cd.cd_doc_id = d.doc_id
   INNER JOIN t_corpus corp ON cd.cd_corp_id = corp.corp_id
   INNER JOIN t_lang_resource lr ON corp.corp_lr_id = lr.lr_id
GROUP BY lr.lr_name
ORDER BY 1;

-- Get all corpus names
SELECT lr.lr_name
FROM t_lang_resource lr 
   INNER JOIN t_lr_type lrt ON lr.lr_type_id = lrt.lrtp_id 
WHERE lrtp_type LIKE '%Corpus%'
ORDER BY 1;

--Get all document names
SELECT lr.lr_name
FROM t_lang_resource lr 
   INNER JOIN t_lr_type lrt ON lr.lr_type_id = lrt.lrtp_id 
WHERE lrtp_type LIKE '%Document%'
ORDER BY 1;

--Get the value of a particular feature in all tickets,
--for example, the TroubleType (string)
SELECT f1.ft_character_value as "TicketNumber"
    , f2.ft_character_value as "TroubleType"
FROM t_feature_key k1
    INNER JOIN t_feature f1 ON f1.ft_key_id = k1.fk_id
    INNER JOIN t_document d ON d.doc_lr_id = f1.ft_entity_id
    INNER JOIN t_feature f2 ON d.doc_lr_id = f2.ft_entity_id
    INNER JOIN t_feature_key k2 ON f2.ft_key_id = k2.fk_id
WHERE k1.fk_string = 'TicketNumber'
    AND k2.fk_string = 'TroubleType';

--Get the value of a particular feature in all tickets,
--for example, the SeriousnessMetadata (integer)
SELECT f1.ft_character_value as "TicketNumber"
    , f2.ft_int_value as "SeriousnessMetadata"
FROM t_feature_key k1
    INNER JOIN t_feature f1 ON f1.ft_key_id = k1.fk_id
    INNER JOIN t_document d ON d.doc_lr_id = f1.ft_entity_id
    INNER JOIN t_feature f2 ON d.doc_lr_id = f2.ft_entity_id
    INNER JOIN t_feature_key k2 ON f2.ft_key_id = k2.fk_id
WHERE k1.fk_string = 'TicketNumber'
    AND k2.fk_string = 'SeriousnessMetadata';

-- Add a feature too all tickets, taking the 
-- values from another table. In this example, we assume the 
-- table TicketTroubleTypes has two columns: TicketNumber and
-- TroubleType. Note that 2 is the language resource type id
-- of a document (from table t_lr_type), and 104 is the 
-- value type of a string feature (104=string, 101=integer, 106=float)
INSERT INTO t_feature(ft_id, ft_entity_id
    , ft_entity_type, ft_key_id, ft_character_value
    , ft_value_type)
SELECT nextval(''seq_feature'')
    , f.ft_entity_id
    , 2, v_feat_key, ttt.troubleType
    , 104
FROM TicketTroubleTypes ttt, t_feature f
WHERE f.ft_character_value = ttt.ticketNumber 
    AND f.ft_key_id = v_ticketNumber_feat_key;
